package org.compiere.process;

import java.io.File;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.CellFormat;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.DateFormat;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.adempiere.exceptions.AdempiereException;
import org.compiere.apps.ADialog;
import org.compiere.model.MAttachmentEntry;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.eevolution.model.X_HR_Department;
import org.eevolution.model.X_HR_Employee;
import org.eevolution.model.X_HR_Job;

import extend.org.compiere.hrm.ExcelCell;
import extend.org.compiere.utils.Util;

public class ReportBirthday extends SvrProcess {
	
	/** Current context		*/
	private Properties m_ctx;
	/**	 */
	private ProcessInfo pi;
	/** */
	private int HR_Department_ID = 0;
	/** */
	private int HR_Block_ID = 0;
	/** */
	private String path = "";
	/** */
	X_HR_Department department = null;
	/** */
	X_HR_Department block = null;
	/** */
	Timestamp startDate;

	@Override
	protected void prepare() {

		log.info(" Report Birthday ");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++)	{
			String name = para[i].getParameterName();
			if (name == null); 
				//
			else if (name.compareTo("HR_Block_ID") == 0 && para[i].getParameter() != null)
				HR_Block_ID = new BigDecimal(Integer.parseInt(para[i].getParameter().toString())).intValue();
			else if (name.compareTo("HR_Department_ID") == 0 && para[i].getParameter() != null)
				HR_Department_ID = new BigDecimal(Integer.parseInt(para[i].getParameter().toString())).intValue();
			else if (name.toLowerCase().endsWith("directory"))
				path = (String)para[i].getParameter();
			else if (name.compareTo("StartDate") == 0)
				startDate = (Timestamp) para[i].getParameter();
			else
			{
				log.log(Level.INFO, "Unknown Parameter: " + name);
			}
		}
		
		if(path.isEmpty())
			path = Util.localFilePath;
		else
			path = path + System.getProperty("file.separator");

		department = new X_HR_Department(getCtx(), HR_Department_ID, null);
		block = new X_HR_Department(getCtx(), HR_Block_ID, null);

	}

	@Override
	protected String doIt() throws Exception {

		// Types of cells for
		Label label;
		Number number;
		DateTime dateCell;
		// There are checks for a specified pattern
		String templateName = "SampleBirthday";
		
		//
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, templateName);
		if(entry == null)
			throw new AdempiereException(Msg.translate(m_ctx, "NotFoundTemplate")+" "+templateName);
				
		// We define a path to generate
		String fileExtension = entry.getName().substring(entry.getName().lastIndexOf("."),entry.getName().length());
		StringBuffer fullPath = new StringBuffer(path); 
		fullPath.append("List of Birthday").append(fileExtension);
				
		// 
		File templateCopy = new File(fullPath.toString());
		File temporaryFile = entry.getFile(Util.localFilePath+templateName);
		Workbook tableBook = null;
		WritableWorkbook copy = null;
		WritableSheet sheet = null;
		 try {
			 tableBook = Workbook.getWorkbook(temporaryFile);
			 copy = Workbook.createWorkbook(templateCopy, tableBook);
			 sheet = copy.getSheet(0);
		} catch (Exception e) {
		  	ADialog.error(999, null, "Error tableBook. " + e.getMessage());
		   	return "Error tableBook.";
		}

		ExcelCell cellStart =  Util.getCellStart(tableBook,">>");
		
		// Standart style cell
	    WritableCellFormat borderStyle = new WritableCellFormat();
	    borderStyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
	    borderStyle.setAlignment(Alignment.CENTRE);
	    borderStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
	    borderStyle.setWrap(true);
	    // Department style cell
	    WritableFont fontDepartment = new WritableFont (WritableFont.TIMES, 12, WritableFont.BOLD);
	    WritableCellFormat cellDepartment = new WritableCellFormat(fontDepartment);
	    cellDepartment.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
	    cellDepartment.setAlignment(Alignment.CENTRE);
	    cellDepartment.setVerticalAlignment(VerticalAlignment.CENTRE);
	    cellDepartment.setBackground(Colour.TAN);
	    // Date format Setting
	 	DateFormat customDateFormat = new DateFormat ("dd.MM.yyyy"); 
	 	WritableCellFormat dateFormat = new WritableCellFormat (customDateFormat); 
	 	dateFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
	 	dateFormat.setAlignment(Alignment.CENTRE);
	 	dateFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
	 	// Title style cell
	 	WritableFont fontTitle = new WritableFont (WritableFont.TIMES, 14, WritableFont.NO_BOLD);
	 	WritableCellFormat titleStyle = new WritableCellFormat(fontTitle);
	 	titleStyle.setAlignment(Alignment.CENTRE);
	 	titleStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
	 	
	    // header report
	    WritableCell readCell = sheet.getWritableCell(1, 1);
		CellFormat readFormat = readCell.getCellFormat();
		label = new Label(0, 1, sheet.getCell(0, 1).getContents()+Util.getOrgInfo().getDescription(), readFormat);
		sheet.addCell(label);
	 	
	 	Object[] param = new Object[]{Util.getMonthName(new Timestamp(startDate.getTime()), Env.getAD_Language(getCtx()), false), 
				  Integer.toString(new org.joda.time.DateTime(startDate.getTime()).getYear())};
	 	
	 	label = new Label(2, 2, MessageFormat.format(" на {0} месяц {1} года", param), titleStyle);
		sheet.addCell(label);
		
		X_HR_Employee[] lines = getValues();
		X_HR_Job job = null;
		int col = cellStart.getC(); // offset by the number of first columns
		int row = cellStart.getR();
		int dId = 0; // Departmnet ID Merge cells
		int dep = 0;
		int num = 1;
		int i = 0;
		for(int j = row; j < (row+lines.length); j++){
			if(dId != lines[i].getHR_Department_ID()){
				dId = lines[i].getHR_Department_ID();
				sheet.insertRow(j+dep);
				sheet.mergeCells(1, j+dep, 5, j+dep);
				sheet.setRowView(j+dep, 600);
				label = new Label(1, j+dep, lines[i].getHR_Department().getName(), cellDepartment); 
				sheet.addCell(label);
				dep++;
			}   
			
			job = new X_HR_Job(getCtx(), lines[i].getHR_Job_ID(), null);
			
			sheet.setRowView(j+dep, 550);
	    	 // #
	    	 number = new Number(cellStart.getC(), j+dep, num, borderStyle); 
	    	 sheet.addCell(number);
	    	 // initials
	    	 label = new Label(col+1, j+dep, lines[i].getC_BPartner().getName(), borderStyle); 
	    	 sheet.addCell(label);
	    	 // Job
	    	 label = new Label(col+2, j+dep, job.getName(), borderStyle); 
	    	 sheet.addCell(label);
	    	 // Dates
	    	 dateCell = new DateTime(col+3, j+dep, lines[i].gethrm_dateOfBirth(), dateFormat);
	    	 sheet.addCell(dateCell);
	    	 // Age
	    	 org.joda.time.DateTime birthDate = new org.joda.time.DateTime(lines[i].gethrm_dateOfBirth().getTime());
	    	 org.joda.time.DateTime currentDate = new org.joda.time.DateTime(Env.getContextAsDate(getCtx(), "#Date").getTime());
	    	 org.joda.time.DateTime chekDate = new org.joda.time.DateTime(currentDate.getYear()+"-"+birthDate.getMonthOfYear()+"-"+birthDate.getDayOfMonth());
	    	 number = new Number(col+4, j+dep, Util.getAge(lines[i].gethrm_dateOfBirth(), new Timestamp(chekDate.getMillis())), borderStyle); 
	    	 sheet.addCell(number);
			
			i++;
			num++;
		}
		
		copy.write();
	    copy.close();
	    tableBook.close();
	    temporaryFile.delete();
	    
		// Open the file with the application associated
		String fileName = fullPath.toString();
		Util.launchFile(new File(fileName));
		
	    return Msg.translate(m_ctx, "Success");
	}
	
	private X_HR_Employee[] getValues(){
		
		X_HR_Employee hremp = new X_HR_Employee(getCtx(), 0, get_TrxName());
		List<X_HR_Employee> list = new ArrayList<X_HR_Employee>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;		
				
		try
		{
			pstmt = DB.prepareStatement(getSql(),null);
			if (HR_Department_ID != 0){
				pstmt.setInt(1, HR_Department_ID);
			}
			else if(HR_Block_ID !=0){
				pstmt.setInt(1, HR_Block_ID);
			}
			rs = pstmt.executeQuery();	
					
			while (rs.next ())
			{
				hremp = new X_HR_Employee(getCtx(), rs, get_TrxName());
				list.add(hremp);
			}
		}
		catch (SQLException e)
		{
			log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}	
		
		return list.toArray(new X_HR_Employee[list.size()]);
	}
	
	private String getSql(){
		
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT r.* FROM c_bpartner t \n");
		sql.append("JOIN hr_employee r ON r.c_bpartner_id=t.c_bpartner_id \n");
		sql.append("AND r.enddate = (select max(tt.enddate) from hr_employee tt where tt.c_bpartner_id=r.c_bpartner_id) \n");
		sql.append("WHERE t.isEmployee='Y' AND to_char(r.hrm_dateofbirth,'MM') = to_char("+DB.TO_DATE(startDate, true)+",'MM') \n" );
		
		if (HR_Department_ID != 0){
			sql.append(" AND r.hr_department_id=?");
		}
		else if(HR_Block_ID !=0){
			sql.append(" AND r.hr_department_id IN (SELECT node_id FROM ad_treenodedep WHERE parent_id=?)");
		}

		return sql.toString();
	}

}
